package com.augurit.gzcgwsp.excelimportexport.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.gzcgwsp.common.exp.PoiExcelSheetCopy;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkPzrqgyzdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqjszdjb;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkCyryzkdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkGdyjgqjgdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkImportservice;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkJbxxbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkPzrqgyzdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkQysbclmcService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqjszdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqjszrypbqkService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqjszzysbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqqxdylbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqqxsbylbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqqxssdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqssdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqssylbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRypbqkService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkZyaqjsglryzkService;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalPzrqgyzdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqjszdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqssylb;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalPzrqgyzdjbService;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalRqjszdjbService;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalRqjszrypbqkService;
import com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm;
import com.augurit.gzcgwsp.ssylb.entity.YwRqRqssylb;
import com.augurit.gzcgwsp.ywcmn.entity.YwComFinalQyxx;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxFinalService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxService;


/**
 * excel导入
 * @author xgw
 *
 */
@Service
@Transactional
public class YwRqQyjyxkImportserviceImpl implements IYwRqQyjyxkImportservice {
	private final static Logger log=Logger.getLogger(YwRqQyjyxkImportserviceImpl.class);
	@Autowired
	private IYwRqQyjyxkJbxxbService ywRqQyjyxkJbxxbService;
	
	@Autowired
	private IYwRqQyjyxkGdyjgqjgdjbService ywRqQyjyxkGdyjgqjgdjbService;
	
	@Autowired
	private IYwRqQyjyxkCyryzkdjbService ywRqQyjyxkCyryzkdjbService;
	
	@Autowired
	private IYwRqQyjyxkZyaqjsglryzkService ywRqQyjyxkZyaqjsglryzkService;
	
	@Autowired
	private IYwRqQyjyxkRqqxssdjbService ywRqQyjyxkRqqxssdjbService;
	
	@Autowired
	private IYwRqQyjyxkRqqxdylbService ywRqQyjyxkRqqxdylbService;
	
	@Autowired
	private IYwRqQyjyxkRqqxsbylbService ywRqQyjyxkRqqxsbylbService;
	
	@Autowired
	private IYwRqQyjyxkRqssdjbService ywRqQyjyxkRqssdjbService;
	
	@Autowired
	private IYwRqQyjyxkRqssylbService ywRqQyjyxkRqssylbService;
	
	@Autowired
	private IYwRqQyjyxkQysbclmcService ywRqQyjyxkQysbclmcService;
	
	@Autowired
	private IYwRqQyjyxkPzrqgyzdjbService ywRqQyjyxkPzrqgyzdjbService;
	@Autowired
	private IYwRqQyjyxkFinalPzrqgyzdjbService pzrqgyService;
	@Autowired
	private IYwRqQyjyxkRypbqkService ywRqQyjyxkRypbqkService;
	
	@Autowired
	private IYwRqQyjyxkRqjszdjbService ywRqQyjyxkRqjszcpzqcjqzdjbService;
	@Autowired
	private IYwRqQyjyxkFinalRqjszdjbService rqjszdjbService;
	
	@Autowired
	private IYwRqQyjyxkRqjszrypbqkService ywRqQyjyxkRqjszrypbqkService;
	@Autowired
	private IYwRqQyjyxkFinalRqjszrypbqkService rqjszrypbqkService;
	@Autowired
	private IYwRqQyjyxkRqjszzysbService ywRqQyjyxkRqjszzysbService;
    @Autowired
    private IYwComQyxxFinalService ywComQyxxFinalService;
    
    @Autowired
     private IYwComQyxxService ywComQyxxService;
	/**
	 * excel导入
	 * @param qyid
	 * @param acceptNo
	 * @param username
	 * @param params
	 * @param uploadFile
	 */
	public void importExcel(long rqjyxkid,long qyid,String acceptNo,String username,String params,InputStream uploadFile,String contextpath) throws Exception{
		
			// 通过得到的文件输入流inputstream创建一个HSSFWordbook对象
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(uploadFile);// 将is流实例到
			// 一个excel流里
			HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
			HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
			Date dateFlag = new Date();
			int flag = 0;
			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			int countnum= hssfworkbook.getNumberOfSheets();
			for (int i = 0; i < countnum; i++) {
				flag++;
				hssfsheet = hssfworkbook.getSheetAt(i);
				String sheetName = hssfsheet.getSheetName();
				
				sheetName = sheetName.substring(0, 1);
//				System.out.println("sheet的名字 = "+sheetName);
				if(i==1){//解析、保存 一、基本情况
					String msg = ywRqQyjyxkJbxxbService.parseJbxxb(hssfsheet, acceptNo, qyid,username,"");
					//ywRqQyjyxkJbxxbService.save(ywRqQyjyxkJbxxb);
				}
				else if(i==2){//解析、保存 二、股东以及股权结构登记表
					ywRqQyjyxkGdyjgqjgdjbService.parseGdyjgqjgdjb(hssfsheet, acceptNo, qyid, username);
				}
				
				else if(i==3){//解析、保存 三、从业人员状况登记表
					ywRqQyjyxkCyryzkdjbService.parseCyryzkdjb(hssfsheet, acceptNo, qyid, username);
					ywRqQyjyxkZyaqjsglryzkService.parseZyaqjsglryzk(hssfsheet, acceptNo, qyid, username);
				}
				else if(i==4){//解析、保存 四、燃气抢险设施登记表
					ywRqQyjyxkRqqxssdjbService.parseRqqxssdjb(hssfsheet, acceptNo, qyid, username);
					ywRqQyjyxkRqqxdylbService.parseRqqxdylb(hssfsheet, acceptNo, qyid, username);
					ywRqQyjyxkRqqxsbylbService.parseRqqxsbylb(hssfsheet, acceptNo, qyid, username);
				}
				else if(i==5){//解析、保存 五、燃气设施登记表
					ywRqQyjyxkRqssdjbService.parseRqqxsbylb(hssfsheet, acceptNo, qyid, username);
					ywRqQyjyxkRqssylbService.parseRqqxsbylb(hssfsheet,rqjyxkid, acceptNo, qyid, username);
				}
				else if("八".equals(sheetName)){//解析、保存 八、企业申报材料目录
					ywRqQyjyxkQysbclmcService.parseQysbclml(hssfsheet, acceptNo, qyid, username);
				}
//				else if(i==6){//解析、保存  六、燃气接收站、储配站、汽车加气站登记表
				else if("六".equals(sheetName)){
					ywRqQyjyxkRqjszcpzqcjqzdjbService.parseRqjszdjb(hssfsheet, acceptNo, qyid, username, dateFlag,  Integer.toString(flag),contextpath,rqjyxkid);
					//ywRqQyjyxkRqjszrypbqkService.parseRqjszrypbqk(hssfsheet, acceptNo, qyid, username, dateFlag,Integer.toString(flag));
					//ywRqQyjyxkRqjszzysbService.parseRqjszzysb(hssfsheet, acceptNo, qyid, username, dateFlag,Integer.toString(flag));
					//flag++;
				}
//				else if(i==7){//解析、保存 七、瓶装燃气供应站登记表
				else if("七".equals(sheetName)){					
					ywRqQyjyxkPzrqgyzdjbService.parsePzrqgyzdjb(hssfsheet,acceptNo, qyid, username,dateFlag, Integer.toString(flag),contextpath,rqjyxkid);
					//ywRqQyjyxkRypbqkService.parseRypbqk(hssfsheet, acceptNo, qyid, username, dateFlag,  Integer.toString(flag));
					//flag++;
				}
				System.out.println(i+"的总行数++++++++" + hssfsheet.getPhysicalNumberOfRows());
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
					hssfrow = hssfsheet.getRow(j);
					// 判断是否还存在需要导入的数据
					if (hssfrow == null) {
//						System.out.println("这里已没有数据，在第" + i + "列,第" + j + "行");
						break;
					}
				}
			}
				
	}
	/**
     * excel导出
     * 
     * @param qyid
     * @param acceptNo
     * @param username
     * @param params
     * @param uploadFile
     */
    public String exportExcel(long qyid, String acceptNo, String username,
            String params,String path,Long ajid,String exptype) throws Exception {
        HSSFWorkbook workbook =null;
        InputStream is = null;
        String temp=path+File.separator+"Templates"+File.separator+"exptest.xls";
       /** add 创建新文件路径 */
        String formatDateX = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
		int randomX = new Random().nextInt(1000000);
		String directory=path+File.separator+"Templates"+File.separator+"modelTemplate";
		String model =directory+File.separator+formatDateX+randomX+".xls";
		System.out.println("导出的模板路径:"+model);
		log.info("导出的模板路径:"+model);
		File file=new File(model);
		File dir=new File(directory);
		if(!dir.isDirectory()){
			dir.mkdir();
		}
		if (!file.getParentFile().exists()) {   
            if (!file.getParentFile().mkdirs()) {   
            	log.info("导出创建文件夹失败");
            }   
        }
        if(!file.exists()){
        	boolean fileflag=file.createNewFile();
    		log.info("创建导出的模板路径creatfile------>:"+fileflag);
        }
		boolean bool = PoiExcelSheetCopy.copyFile(temp, model);
		log.info("复制文件是否成功:"+bool);
       if(!bool)return null;
       /** end 创建新文件路径 */
        try {
        	is=new FileInputStream(model);
            workbook = new HSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            is.close();
        }
        
        boolean sex_bool = false;
        boolean seven_bool = false;
     // 一个excel流里    
        List<YwRqQyjyxkRqjszdjb> list6 = ywRqQyjyxkRqjszcpzqcjqzdjbService.getListByAcceptNo(acceptNo);
        List<YwRqQyjyxkFinalRqjszdjb> listdata6 = rqjszdjbService.getInfosByQyid(qyid);
        List<YwRqQyjyxkPzrqgyzdjb> list7 = ywRqQyjyxkPzrqgyzdjbService.getListByAcceptNo(acceptNo);
        List<YwRqQyjyxkFinalPzrqgyzdjb> listdata7 = pzrqgyService.getListByQyId(qyid);
        if(workbook!=null)
        {
        HSSFSheet hssfsheet = workbook.getSheetAt(0);// 第一个工作表
        HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
        // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            hssfsheet = workbook.getSheetAt(i);
            String sheetName = hssfsheet.getSheetName();
            String totalSheetName=sheetName;
            sheetName = sheetName.substring(0, 1);
            PoiExcelSheetCopy poiSheetCopy=new PoiExcelSheetCopy();
            if ("六".equals(sheetName)){
                if(list6!=null&&list6.size()>1)
                {
                	for(int n = 1; n < list6.size(); n++){
                		poiSheetCopy.copySheet(totalSheetName,totalSheetName+"_"+n,model);
                	}
                }
            }
            else if ("七".equals(sheetName)) {
            	
            	 if(list7!=null&&list7.size()>1)
            	 {
                 	for(int n = 1; n < list7.size(); n++){
                 		poiSheetCopy.copySheet(totalSheetName,totalSheetName+"_"+n,model);
                 	}
            	 }
            
            }
        
           }
        }
        
        try {
        	is=new FileInputStream(model);
         workbook = new HSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            is.close();
        }
        

        HSSFCellStyle   style   =   workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont font = workbook.createFont();
        font.setFontName("仿宋_GB2312");
        font.setFontHeight((short)240);
        font.setCharSet(HSSFFont.DEFAULT_CHARSET);
        style.setFont(font);
        // 一个excel流里
        HSSFSheet hssfsheet = workbook.getSheetAt(0);// 第一个工作表
        HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
        // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
        
        if("qyxx".equals(exptype)){//查看企业信息
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                hssfsheet = workbook.getSheetAt(i);
                String sheetName = hssfsheet.getSheetName();
                String totalSheetName=sheetName;
                sheetName = sheetName.substring(0, 1);
                if (i == 0) {// 解析、读取、导出 一、基本情况
                    ywRqQyjyxkJbxxbService.expJbxxb(hssfsheet, acceptNo, qyid, username,style,exptype);
                } else if (i == 1) {// 解析、读取、导出 二、股东以及股权结构登记表
                    ywRqQyjyxkGdyjgqjgdjbService.expGdyjgqjgdjb(hssfsheet, acceptNo, qyid, username,style,exptype);
                } else if (i == 2) {// 解析、读取、导出 三、从业人员状况登记表
                    ywRqQyjyxkCyryzkdjbService.expCyryzkdjb(hssfsheet, acceptNo, qyid, username,style,exptype);
                    ywRqQyjyxkZyaqjsglryzkService.expZyaqjsglryzk(hssfsheet, acceptNo, qyid, username,style,exptype);
                } else if (i == 3) {// 解析、读取、导出 四、燃气抢险设施登记表
                    ywRqQyjyxkRqqxssdjbService.expRqqxssdjb(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                   ywRqQyjyxkRqqxdylbService.expRqqxdylb(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                    ywRqQyjyxkRqqxsbylbService.expRqqxsbylb(hssfsheet, acceptNo,
                            qyid, username,style,exptype); 
                } else if (i == 4) {// 解析、读取、导出 五、燃气设施登记表
                    ywRqQyjyxkRqssdjbService.expRqqxsbylb(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                    ywRqQyjyxkRqssylbService.expRqqxsbylb(hssfsheet, acceptNo,
                            qyid, username,style,ajid,exptype);
                } else if (i == 7) {// 解析、读取、导出 八、企业申报材料目录
                    ywRqQyjyxkQysbclmcService.expQysbclml(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                } else if ("六".equals(sheetName)) {
                           if(listdata6 != null && listdata6.size() > 0){
                            int index = totalSheetName.lastIndexOf("_");
                                if(index < 1){
                                    ywRqQyjyxkRqjszcpzqcjqzdjbService.expRqjszdjb(hssfsheet,listdata6.get(0),null,style,exptype);
                                   if(list6.size()>0){
                                	   ywRqQyjyxkRqjszrypbqkService.expRqjszrypbqk(hssfsheet,listdata6.get(0),
                                               acceptNo,qyid,username,list6.get(0).getFlag(),style,exptype);
                                	   ywRqQyjyxkRqjszzysbService.expRqjszrysb(hssfsheet,listdata6.get(0),
                                               acceptNo,qyid,username,list6.get(0).getFlag(),style,exptype);
                                   }else{
                                	   ywRqQyjyxkRqjszrypbqkService.expRqjszrypbqk(hssfsheet,listdata6.get(0),
                                               acceptNo,qyid,username,null,style,exptype);
                                	   ywRqQyjyxkRqjszzysbService.expRqjszrysb(hssfsheet,listdata6.get(0),
                                               acceptNo,qyid,username,null,style,exptype);
                                   }
                                   
                                    
                                }else{
                                String t = totalSheetName.substring(index+1, totalSheetName.length());
                                if(listdata6.size() > 1){
                                    for(int n = 0; n < list6.size(); n++){
                                        if(t == null) continue;
                                    int cc = Integer.parseInt(t.trim());
                                     if(t != null && n == cc){
                                         ywRqQyjyxkRqjszcpzqcjqzdjbService.expRqjszdjb(workbook.getSheet(totalSheetName),listdata6.get(n), null,style,exptype);
                                         ywRqQyjyxkRqjszrypbqkService.expRqjszrypbqk(workbook.getSheet(totalSheetName),listdata6.get(n),
                                           acceptNo, qyid, username, list6.get(n).getFlag(),style,exptype);
//                                         ywRqQyjyxkRqjszzysbService.expRqjszrysb(hssfsheet,listdata6.get(n),
//                                                 acceptNo,qyid,username,list6.get(n).getFlag(),style,exptype);
                                     }
                                  }
                                }
                             }
                           }
                }else if ("七".equals(sheetName)) {
                        if(listdata7 != null && listdata7.size() > 0){
                            int index = totalSheetName.lastIndexOf("_");
                            if(index < 1){
                            ywRqQyjyxkPzrqgyzdjbService.expPzrqgyzdjb(hssfsheet,listdata7.get(0),list7.get(0),style, exptype);
                            ywRqQyjyxkRypbqkService.expRypbqk(hssfsheet,listdata7.get(0), acceptNo, qyid,username,style, exptype);
                            }else{
                                String t = totalSheetName.substring(index+1, totalSheetName.length());
                                if(list7.size() > 1){
                                    for(int n = 1; n < list7.size(); n++){
                                    //创建一个sheel
                                   if(t == null) continue;
                                    int cc = Integer.parseInt(t.trim());
                                     if(t != null && n == cc){
                                         ywRqQyjyxkPzrqgyzdjbService.expPzrqgyzdjb(workbook.getSheet(totalSheetName),listdata7.get(0), list7.get(n),style, exptype);
                                         ywRqQyjyxkRypbqkService.expRypbqk(workbook.getSheet(totalSheetName),list7.get(n), acceptNo, qyid,
                                             username,style, exptype);
                                     }
                                  }
                                }
                            }
                        }
                   
                }
            }
            
        }else{
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                hssfsheet = workbook.getSheetAt(i);
                String sheetName = hssfsheet.getSheetName();
                String totalSheetName=sheetName;
                sheetName = sheetName.substring(0, 1);
                if (i == 1) {// 解析、读取、导出 一、基本情况
                    ywRqQyjyxkJbxxbService.expJbxxb(hssfsheet, acceptNo, qyid, username,style,exptype);
                } else if (i == 2) {// 解析、读取、导出 二、股东以及股权结构登记表
                    ywRqQyjyxkGdyjgqjgdjbService.expGdyjgqjgdjb(hssfsheet, acceptNo, qyid, username,style,exptype);
                } else if (i == 3) {// 解析、读取、导出 三、从业人员状况登记表
                    ywRqQyjyxkCyryzkdjbService.expCyryzkdjb(hssfsheet, acceptNo, qyid, username,style,exptype);
                    ywRqQyjyxkZyaqjsglryzkService.expZyaqjsglryzk(hssfsheet, acceptNo, qyid, username,style,exptype);
                } else if (i == 4) {// 解析、读取、导出 四、燃气抢险设施登记表
                    ywRqQyjyxkRqqxssdjbService.expRqqxssdjb(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                   ywRqQyjyxkRqqxdylbService.expRqqxdylb(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                    ywRqQyjyxkRqqxsbylbService.expRqqxsbylb(hssfsheet, acceptNo,
                            qyid, username,style,exptype); 
                } else if (i == 5) {// 解析、读取、导出 五、燃气设施登记表
                    ywRqQyjyxkRqssdjbService.expRqqxsbylb(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                    ywRqQyjyxkRqssylbService.expRqqxsbylb(hssfsheet, acceptNo,
                            qyid, username,style,ajid,exptype);
                } else if (i == 8) {// 解析、读取、导出 八、企业申报材料目录
                    ywRqQyjyxkQysbclmcService.expQysbclml(hssfsheet, acceptNo,
                            qyid, username,style,exptype);
                } else if ("六".equals(sheetName)) {
                           if(list6 != null && list6.size() > 0){
                            int index = totalSheetName.lastIndexOf("_");
                                if(index < 1){
                                    ywRqQyjyxkRqjszcpzqcjqzdjbService.expRqjszdjb(hssfsheet,null,list6.get(0),style,exptype);
                                    ywRqQyjyxkRqjszrypbqkService.expRqjszrypbqk(hssfsheet,list6.get(0),
                                            acceptNo,qyid,username,list6.get(0).getFlag(),style,exptype);
                                    ywRqQyjyxkRqjszzysbService.expRqjszrysb(hssfsheet,list6.get(0),
                                            acceptNo,qyid,username,list6.get(0).getFlag(),style,exptype);
                                }else{
                                String t = totalSheetName.substring(index+1, totalSheetName.length());
                                if(list6.size() > 1){
                                    for(int n = 1; n < list6.size(); n++){
                                        if(t == null) continue;
                                    int cc = Integer.parseInt(t.trim());
                                     if(t != null && n == cc){
                                         ywRqQyjyxkRqjszcpzqcjqzdjbService.expRqjszdjb(workbook.getSheet(totalSheetName),null, list6.get(n),style,exptype);
                                         ywRqQyjyxkRqjszrypbqkService.expRqjszrypbqk(hssfsheet,list6.get(n),
                                                 acceptNo,qyid,username,list6.get(0).getFlag(),style,exptype);
                                         ywRqQyjyxkRqjszzysbService.expRqjszrysb(hssfsheet,list6.get(n),
                                                 acceptNo,qyid,username,list6.get(0).getFlag(),style,exptype);
                                     }
                                  }
                                }
                                }
                           }
                 
                }
                // else if(i==7){//解析、读取、导出 七、瓶装燃气供应站登记表
                else if ("七".equals(sheetName)) {  
                        if(list7 != null && list7.size() > 0){
                            int index = totalSheetName.lastIndexOf("_");
                            if(index < 1){
                            ywRqQyjyxkPzrqgyzdjbService.expPzrqgyzdjb(hssfsheet, null,list7.get(0),style, exptype);
                            ywRqQyjyxkRypbqkService.expRypbqk(hssfsheet,list7.get(0), acceptNo, qyid,username,style, exptype);
                            }else{
                                String t = totalSheetName.substring(index+1, totalSheetName.length());
                                if(list7.size() > 1){
                                    for(int n = 1; n < list7.size(); n++){
                                    //创建一个sheel
                                   if(t == null) continue;
                                    int cc = Integer.parseInt(t.trim());
                                     if(t != null && n == cc){
                                         ywRqQyjyxkPzrqgyzdjbService.expPzrqgyzdjb(workbook.getSheet(totalSheetName),null, list7.get(n),style, exptype);
                                         ywRqQyjyxkRypbqkService.expRypbqk(workbook.getSheet(totalSheetName), list7.get(n),acceptNo, qyid,
                                             username,style, exptype);
                                     }
                                  }
                                }
                            }
                        }
                }
            }
        }
        
		String formatDate = new SimpleDateFormat("yyyyMMddHHmmss")
				.format(new Date());
		int random = new Random().nextInt(1000000);
		String targetFilePath =directory+File.separator+formatDate+random+".xls";
		File targetFile=new File(targetFilePath);
		File dirs=new File(directory);
		if(!dirs.isDirectory()){
			dirs.mkdir();
		}
		if(!targetFile.exists()){
			targetFile.createNewFile();
		}
		FileOutputStream fos = new FileOutputStream(targetFilePath);
	    workbook.write(fos);
        File modelFl=new File(model);
        //删除临时模板
      PoiExcelSheetCopy.delFile(modelFl);
      fos.close();
        return targetFilePath;

    }
    
    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkImportservice#importFinalData(java.util.Map)
     */
    public String importFinalData(YwRqQyjyxkForm form,Map map) {
       String filepath=(String) map.get("filepath");
       String acceptNo=(String) map.get("acceptNo");
       String qyid=(String) map.get("qyid");
       String contextpath=(String) map.get("contextPath");
       String result="";
       try {
           InputStream is=new FileInputStream(filepath);
           HSSFWorkbook hssfworkbook = new HSSFWorkbook(is);// 将is流实例到
           // 一个excel流里
           HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
           HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
           Date dateFlag = new Date();
           int flag = 0;
           // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
           int countnum= hssfworkbook.getNumberOfSheets();
           if(countnum>=10){
               hssfsheet = hssfworkbook.getSheetAt(1);
               //解析、保存 一、基本情况
               //result=ywRqQyjyxkJbxxbService.parseJbxxb(hssfsheet, acceptNo, Long.valueOf(qyid),"","cksa");  
               InputStream istream=new FileInputStream(filepath);
               this.importExcel(form.getId(),form.getQyid(),form.getAcceptNo(),"","",istream,contextpath); 
           }
    } catch (Exception e) {
        log.error("文件不存在，系统出现错误导入失败"+e);
        result="文件不存在，系统出现错误导入失败\n";
    } 
        return result;
    }
    
    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkImportservice#importFinalQyData(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm, java.util.Map)
     */
    public Map importFinalQyData(YwRqQyjyxkForm form, Map map) {
        Long qyid=form.getQyid();
        Long id=form.getId();
        String acceptNo=form.getAcceptNo();
        String sqdw=(String) map.get("sqdw");//如果出现多个申请单位的问题避免出现空值
        YwComFinalQyxx finalQyxx=this.ywComQyxxFinalService.getEntityByQyId(qyid);
        if(finalQyxx==null){//基本信息
            finalQyxx=new YwComFinalQyxx();
        }
        finalQyxx.setQyid(qyid);
        finalQyxx=convertQyxxToForm(form,finalQyxx,sqdw);
        if(StringUtils.isBlank(finalQyxx.getSqdw())){
            finalQyxx.setSqdw(sqdw);
        }
        this.ywComQyxxFinalService.saveOrUpdateEntity(finalQyxx);
        //燃气设施一览表
        this.importSsYlb(id,qyid);
        //股东导入
        this.importGdInfo(acceptNo, qyid);
        //从业人员
        this.ywRqQyjyxkCyryzkdjbService.importFinalQyData(acceptNo, qyid);
        //主要安全、技术管理人员状况
        ywRqQyjyxkZyaqjsglryzkService.importFinalQyData(acceptNo, qyid);
        //燃气抢险设施登记表
        ywRqQyjyxkRqqxssdjbService.importFinalQyData(acceptNo, qyid);
        //燃气抢险点登记表
        ywRqQyjyxkRqqxdylbService.importFinalQyData(acceptNo, qyid);
        //燃气抢险设备登记表
       ywRqQyjyxkRqqxsbylbService.importFinalQyData(acceptNo, qyid);
        //燃气燃气设施登记表
       ywRqQyjyxkRqssdjbService.importFinalQyData(acceptNo, qyid);
        //燃气接收站、储配站、汽车加气站登记表
       ywRqQyjyxkRqjszcpzqcjqzdjbService.importFinalQyData(acceptNo, qyid);
        //燃气燃气接收站、储配站、汽车人员配备
       //ywRqQyjyxkRqjszrypbqkService.importFinalQyData(acceptNo, qyid);
        //燃气燃气接收站、储配站、汽车设备
        //ywRqQyjyxkRqjszzysbService.importFinalQyData(acceptNo, qyid);
        //瓶装燃气供应站登记表
       ywRqQyjyxkPzrqgyzdjbService.importFinalQyData(acceptNo, qyid);
        //瓶装燃气供应站登记表 人员配备
        //ywRqQyjyxkRypbqkService.importFinalQyData(acceptNo, qyid);
        //企业材料
       ywRqQyjyxkQysbclmcService.importFinalQyData(acceptNo, qyid);
        
        
        return null;
    }

    /**
     * 设施一览表导入正式数据
     * @param acceptNo
     * @param qyid
     * @return
     */
    private Map importSsYlb(Long id,Long qyid){
        //燃气设施一览表
        List<YwRqRqssylb> list=ywRqQyjyxkRqssylbService.getssList(id);
        for(YwRqRqssylb entity:list){
            YwRqQyjyxkFinalRqssylb finalentity=new YwRqQyjyxkFinalRqssylb();
            finalentity.setDz(entity.getSsaddress());
            finalentity.setMc(entity.getSsmc());
            finalentity.setQyid(qyid);
            finalentity.setSslb(entity.getSslb());
            finalentity.setFzr(entity.getFzr());
            finalentity.setTotalVol(entity.getTotalVol());
            finalentity.setSingleVol(entity.getSingleVol());
            finalentity.setZdlb(entity.getZdlb());
            finalentity.setSskh(entity.getSskh());
            finalentity.setId(null);
           ywRqQyjyxkRqssylbService.saveFinalEntity(finalentity);
        }
        
        return null;
    }
    /**
     * 股东信息导入
     * @param acceptno
     * @param qyid
     * @return
     */
    private Map importGdInfo(String acceptno,Long qyid){
        return ywRqQyjyxkGdyjgqjgdjbService.impportFinalData( acceptno, qyid);       
        
    }
    
    /**
     * 转化为正式企业信息bean
     * @param entity
     * @return
     */
    private  YwComFinalQyxx convertQyxxToForm(YwRqQyjyxkForm entity,YwComFinalQyxx qyxx,String sqdw) {
        if (entity == null) return null;
       
        String addr=entity.getAddress();
        qyxx.setSqdw(entity.getSqdw());
        qyxx.setAddress(addr);
        qyxx.setClsj(entity.getClsj());
        qyxx.setGszcdjh(entity.getGszcdjh());
        qyxx.setYwlxr(entity.getYwlxr());
        qyxx.setQyphone(entity.getLxdh());
        qyxx.setYwlxrtel(entity.getYwlxrtel());
        qyxx.setEmail(entity.getEmail());
        qyxx.setCz(entity.getCz());
        qyxx.setFddbr(entity.getFddbr());
        qyxx.setFddbrZc(entity.getFddbrZc());
        qyxx.setFddbrZw(entity.getFddbrZw());
        qyxx.setJsfzr(entity.getJsfzr());
        qyxx.setJsfzrZc(entity.getJsfzrZc());
        qyxx.setJsfzrZw(entity.getJsfzrZw());
        qyxx.setAqfzr(entity.getAqfzr());
        qyxx.setAqfzrZc(entity.getAqfzrZc());
        qyxx.setAqfzrZw(entity.getAqfzrZw());
        qyxx.setQyfzr(entity.getZyfzr());
        qyxx.setQyfzrZc(entity.getZyfzrZc());
        qyxx.setQyfzrZw(entity.getZyfzrZw());
        qyxx.setZczj(entity.getZczj());
        qyxx.setJyfw(entity.getJyfw());
        if(entity.getQylx()!=null){
            qyxx.setQylx(String.valueOf(entity.getQylx()));
        }
        
        qyxx.setJyfw(entity.getJyfw());
        qyxx.setJyqy(entity.getGqqy());
        qyxx.setFwdh(entity.getFwdh());
        qyxx.setQxdh(entity.getQxdh());
        qyxx.setQyjs(entity.getQyjj());
        qyxx.setQywz(entity.getQywz());
        
        //YwComFinalQyxx qyxx = new DozerBeanMapper().map(entity, YwComFinalQyxx.class);
             
         return qyxx;
   }
    
    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkImportservice#personData(java.lang.String)
     */
	public String checkPersonData(String filepath,Long qyid) {
		   Workbook wb;
		   String error="";
		   try {
			InputStream is=new FileInputStream(filepath);
			wb=ExcelUtil.getWorkBookByInputStream(is);
			Sheet[] sheets=wb.getSheets();
			Map<Long,String> qymap=ywComQyxxService.getQyxx("'rqjyxkz_pzqy','rqjyxkz_gdqy'");
			 //从业人员状况登记表的检查
			 Sheet sheet=sheets[3];
			 for(int k=28;k<sheet.getRows();k++){
				 String name=ExcelUtil.readData(sheet, 0, k);//姓名
				 String idCard=ExcelUtil.readData(sheet, 1, k);//身份证号码
				 if(StringUtils.isNotBlank(name) && StringUtils.isNotBlank(idCard)){
					 List<Long> list=ywRqQyjyxkZyaqjsglryzkService.findPerson(name,idCard,qyid);
					 if(list!=null && list.size()>0){
						 String qyname="";
						 Set<Long> set=new HashSet<Long>();
						 for(Long id:list){
							 set.add(id);
						 }
						 for(Long id:set){
							 if(StringUtils.isNotBlank(qymap.get(id))
							    &&StringUtils.isNotBlank(qymap.get(qyid)) &&!qymap.get(id).equals(qymap.get(qyid))){
								 qyname+=qymap.get(id)==null?"":qymap.get(id)+",";
							 }
						 }
						 if(StringUtils.isNotBlank(qyname))
							 qyname=qyname.substring(0,qyname.length()-1);
						 
						     error+="从业人员状况登记表第"+k+"行姓名："+name+",身份证号码:"+idCard+"在其他企业存在:"+qyname+"##";
					 }
				 }
			 }
			 //从第6个工作表开始检查其他的人员
			 for(int i=6;i<sheets.length;i++){
				 //检查燃气接收站、储配站、汽车加气站登记表的人员
					 if(sheets[i].getName().indexOf("汽车加气站")>0){
						 for(int k=24;k<sheets[i].getRows();k++){
						 String name=ExcelUtil.readData(sheets[i], 1, k);//姓名
						 String zsbh=ExcelUtil.readData(sheets[i], 4, k);//证书编号
						 if(StringUtils.isNotBlank(name) && StringUtils.isNotBlank(zsbh)){
							 List<Long> list=this.ywRqQyjyxkRqjszrypbqkService.findPersonData(name,zsbh,qyid);
							 if(list!=null && list.size()>0){
								 String qyname="";
								 for(Long id:list){
									 
									 qyname+=qymap.get(id)==null?"":qymap.get(id)+",";
								 }
								 if(StringUtils.isNotBlank(qyname))
									 qyname=qyname.substring(0,qyname.length()-1);
								     error+="燃气接收站、储配站、汽车加气站登记表第"+k+"行姓名："+name+",证书编号:"+zsbh+"在其他企业存在:"+qyname+"##";
							  }
						 }
					 }
				 }
				 if(sheets[i].getName().indexOf("瓶装燃气供应站")>0){
					 for(int k=10;k<sheets[i].getRows();k++){
						 String name=ExcelUtil.readData(sheets[i], 1, k);//姓名
						 String zsbh=ExcelUtil.readData(sheets[i], 4, k);//证书编号
						 if(StringUtils.isNotBlank(name) && StringUtils.isNotBlank(zsbh)){
							 List<Long> list=this.ywRqQyjyxkRypbqkService.findPersonData(name,zsbh,qyid);
							 if(list!=null && list.size()>0){
								 String qyname="";
								 for(Long id:list){
									 qyname+=qymap.get(id)==null?"":qymap.get(id)+",";
								 }
								 if(StringUtils.isNotBlank(qyname)){
									 qyname=qyname.substring(0,qyname.length()-1);
								 }
							   error+="瓶装燃气供应站登记第"+k+"行姓名："+name+",证书编号:"+zsbh+"在在其他企业存在:"+qyname+"##";
					      }
				     }
				 }
				}
			 }
		} catch (Exception e) {
			e.printStackTrace();
			log.error("检查人员时候读取申请表出现错误"+e);
		} 
         
		return error;
	}

	
}
